# -*- coding: utf-8 -*-
"""
@project: AutoTesting_pulsar
@author: chenhh
@file: Mysql_operate.py
@ide: PyCharm
@time: 2021/1/25
"""
import pymysql
import os
from Common.Read_data import data
from Common.Log import logger

BASE_PATH = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
data_file_path = os.path.join(BASE_PATH, "config", "Config.ini")
data = data.load_ini(data_file_path)["mysql"]

DB_CONF = {
    "host": data["mysql_host"],
    "port": int(data["mysql_port"]),
    "user": data["mysql_user"],
    "password": data["mysql_passwd"],
    "db": data["mysql_db"]
}


class MysqlDb():

    def __init__(self, db_conf=None):
        # 通过字典拆包传递配置信息，建立数据库连接
        if db_conf is None:
            db_conf = DB_CONF
        self.conn = pymysql.connect(**db_conf, autocommit=True)
        # 通过 cursor() 创建游标对象，并让查询结果以字典格式输出
        self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def __del__(self):  # 对象资源被释放时触发，在对象即将被删除时的最后操作
        # 关闭游标
        self.cur.close()
        # 关闭数据库连接
        self.conn.close()

    def select_db(self, sql):
        """查询"""
        # 检查连接是否断开，如果断开就进行重连
        self.conn.ping(reconnect=True)
        # 使用 execute() 执行sql
        self.cur.execute(sql)
        # 使用 fetchall() 获取查询结果
        result = self.cur.fetchall()
        return result

    def execute_db(self, sql):
        """更新/新增/删除"""
        try:
            # 检查连接是否断开，如果断开就进行重连
            self.conn.ping(reconnect=True)
            # 使用 execute() 执行sql
            self.cur.execute(sql)
            # 提交事务
            self.conn.commit()
        except Exception as e:
            logger.info("操作MySQL出现错误，错误原因：{}".format(e))
            # 回滚所有更改
            self.conn.rollback()


db = MysqlDb(DB_CONF)


if __name__ == '__main__':
    res = MysqlDb()
    res1 = res.select_db(
        sql="select * from `subject` where  id in ('39fa25b7-5974-daf6-8c5e-9baa9e8a3057')  ORDER BY created_on desc limit 100")
    print(res1)
